﻿---增加油料类型数据字典
INSERT INTO Dictionay VALUES( 13, '油料类型数据字典', '油料类型数据字典')
GO

---增加默认的油料类型
INSERT INTO DictionayItem VALUES( '04062E17-6348-41B1-9E31-1A9DABBCAC04', 13, '0#柴油', '0#CY', NULL, 1 )
INSERT INTO DictionayItem VALUES( '04062E17-6348-41B1-9E31-1A9DABBCAC05', 13, '-10#柴油', '-10#CY', NULL, 2 )
INSERT INTO DictionayItem VALUES( '04062E17-6348-41B1-9E31-1A9DABBCAC06', 13, '93#汽油', '93#QY', NULL, 3 )
INSERT INTO DictionayItem VALUES( '04062E17-6348-41B1-9E31-1A9DABBCAC07', 13, '97#汽油', '97#QY', NULL, 4 )
GO

---更新油料类型
---首先把OilType
UPDATE MaintainMachine SET OilType = '04062E17-6348-41B1-9E31-1A9DABBCAC04'
UPDATE ProjectMachine SET OilType = '04062E17-6348-41B1-9E31-1A9DABBCAC04'

---更新工程计划表
ALTER TABLE [dbo].[ProjectPlan] ADD 
[Amount] [decimal] (18,2) NOT NULL CONSTRAINT [DF_ProjectPlan_Amount] DEFAULT ((0))
GO

---修复工程计划总额
UPDATE ProjectPlan SET Amount = I.Amount
FROM ProjectPlan P
INNER JOIN 
(
	SELECT PlanID, SUM(Amount) AS Amount
	FROM ProjectPlanItem AS I
	GROUP BY PlanID
) AS I
ON P.PlanID = I.PlanID

---更新养护计划表
ALTER TABLE [dbo].[MaintainPlan] ADD 
[Amount] [decimal] (18,3) NOT NULL CONSTRAINT [DF_MaintainPlan_Amount] DEFAULT ((0))
GO

---修复养护计划总额总额
UPDATE MaintainPlan SET Amount = I.Amount
FROM MaintainPlan P
INNER JOIN 
(
	SELECT PlanID, SUM(Amount) AS Amount
	FROM MaintainPlanItem AS I
	GROUP BY PlanID
) AS I
ON P.PlanID = I.PlanID
